-- @owner:yangjian
-- @date:2024-4-2
-- @testpoint:1.去除大对象使用限制，测试lo_put函数使用和参数的表现，合理报错

--清理环境
CREATE OR REPLACE FUNCTION f_opengauss_large_object_function_case0018_1()  
RETURNS VOID AS 
$$
BEGIN  
BEGIN
    PERFORM lo_unlink(100);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(-2147483648);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(4294967295);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(-1);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(200);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(201);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(202);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
END;  
$$
LANGUAGE plpgsql;
/

select f_opengauss_large_object_function_case0018_1();

--环境准备
drop table if exists t_opengauss_large_object_function_case0018_1 cascade;
CREATE TABLE t_opengauss_large_object_function_case0018_1 (loid oid,fd int);
INSERT INTO t_opengauss_large_object_function_case0018_1 (loid) SELECT lo_create(100);
INSERT INTO t_opengauss_large_object_function_case0018_1 (loid) SELECT lo_create(-1);
INSERT INTO t_opengauss_large_object_function_case0018_1 (loid) SELECT lo_create(0);
INSERT INTO t_opengauss_large_object_function_case0018_1 (loid) SELECT lo_create(-2147483648);

--step1:测试loid参数为存在值;expect:成功
select lo_put(100,6,'aaaaaa');

--step2:测试loid参数为不存在值;expect:失败
select lo_put(200,6,'aaaaaa');

--step3:测试loid参数为非int类型;expect:失败
select lo_put('a',6,'aaaaaa');

--step4:测试loid参数为负数;expect:成功
select lo_put(-1,6,'aaaaaa');

--step5:测试loid参数为空值;expect:成功
select lo_put(null,6,'aaaaaa');

--step6:测试loid参数超过oid边界值;expect:失败
select lo_put(4294967296,6,'aaaaaa');
select lo_put(-2147483649,6,'aaaaaa');

--step7:测试loid参数为oid边界值;expect:成功
select lo_put(4294967295,6,'aaaaaa');
select lo_put(-2147483648,6,'aaaaaa');

--step8:测试loid参数为空串;expect:成功
select lo_put('',6,'aaaaaa');

--step9:测试loid参数为0;expect:成功
select lo_put(0,6,'aaaaaa');

--step10:测试date参数为非bytea类型;expect:失败
select lo_put(100,6,1);

--step11:测试date参数含中文;expect:成功
select lo_put(100,6,'测试');

--step12:测试offset参数为非int值;expect:失败
select lo_put(100,'a','aaaaaa');

--step13:测试offset参数为0;expect:成功
select lo_put(100,0,'aaaaaa');

--step14:测试offset参数为负数;expect:成功
select lo_put(100,-1,'aaaaaa');

--step15:测试offset参数为空值;expect:成功
select lo_put(100,null,'aaaaaa');

--step16:测试offset参数为空串;expect:成功
select lo_put(100,'','aaaaaa');

--step17:测试offset参数为int8边界值;expect:失败
select lo_put(100,9223372036854775807,'aaaaaa');
select lo_put(100,-9223372036854775808,'aaaaaa');

--step18:测试offset参数超int8边界值;expect:失败，报错函数不存在
select lo_put(100,9223372036854775808,'aaaaaa');
select lo_put(100,-9223372036854775809,'aaaaaa');

--清理环境
select lo_unlink(loid) from t_opengauss_large_object_function_case0018_1;
select f_opengauss_large_object_function_case0018_1();
drop table if exists t_opengauss_large_object_function_case0018_1 cascade;
drop function f_opengauss_large_object_function_case0018_1();

